﻿CREATE PROCEDURE [dbo].[ASMS_SP_QueryMaterialPagedList]
	@filter nvarchar(64),
	@businessId uniqueidentifier,
	@stationId uniqueidentifier,
	@category nvarchar(64),
	@brand nvarchar(64),
	@status int = null,
	@start int,
	@limit int,
	@total int output
AS
SELECT T.* from (
	SELECT ROW_NUMBER() over(order by SI.Name) as _RowIndex, SI.Barcode,
		SI.Brand,
		SI.BusinessId,
		SI.Category,
		SI.Code,
		case when SRS.Cost is not null then SRS.Cost
			when SRB.Cost is not null then SRB.Cost
			else SI.Cost end as Cost,
		SI.CreatedTime,
		SI.Creator,
		SI.[Description],
		SI.Id,
		SI.Letter,
		SI.Name,
		SI.Model,
		SI.Pinyin,
		case when SRS.Price is not null then SRS.Price
			when SRB.Price is not null then SRB.Price
			else SI.Price end as Price,
		case when SRS.SalesCommission is not null then SRS.SalesCommission
			when SRB.SalesCommission is not null then SRB.SalesCommission
			else SI.SalesCommission end as SalesCommission,
		case when SRS.SalesCommissionType is not null then SRS.SalesCommissionType
			when SRB.SalesCommissionType is not null then SRB.SalesCommissionType
			else SI.SalesCommissionType end as SalesCommissionType,
		SI.QuickCode,
		SI.Unit,
		SI.StationId,
		SI.[Status],
		case when SRS.TaxRate is not null then SRS.TaxRate
			when SRB.TaxRate is not null then SRB.TaxRate
			else SI.TaxRate end as TaxRate,
		MDIMC.Name as CategoryName,
		MDIMU.Name as UnitName,
		MDIMB.Name as BrandName
	from ASMS_Material as SI
		left join ASMS_MasterDataItem as MDIMC on MDIMC.Code = SI.Category
		left join ASMS_MasterDataItem as MDIMU on MDIMU.Code = SI.Unit
		left join ASMS_MasterDataItem as MDIMB on MDIMB.Code = SI.Brand
		left join ASMS_MaterialRetain SRB on SRB.MaterialId = SI.Id and SRB.BusinessId = @businessId and SRB.StationId is null
		left join ASMS_MaterialRetain SRS on SRS.MaterialId = SI.Id and SRS.BusinessId = @businessId and SRS.StationId = @stationId
	where (@category is null or SI.Category = @category) and (@brand is null or Brand = @brand)  and (@status is null or SI.[Status] = @status) and (@filter is null or SI.Name like '%'+@filter +'%' )) as T
where T._RowIndex > @start and T._RowIndex <= @start + @limit

select @total = (select count(Id) from ASMS_Material where  (@category is null or Category = @category) and (@brand is null or Brand = @brand) and (@status is null or [Status] = @status) and (@filter is null or Name like '%'+@filter +'%' ));
RETURN 0
